package com.example.linearlayoutdemo;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import java.util.ArrayList;
import java.util.List;

public class BookDatabaseHelper extends SQLiteOpenHelper {
    // 数据库常量
    public static final String DATABASE_NAME = "BookDatabase.db";
    public static final int DATABASE_VERSION = 1;
    public static final String TABLE_BOOK = "book";

    // 字段常量
    public static final String COL_ID = "_id";
    public static final String COL_BOOK_NUMBER = "book_number";
    public static final String COL_BOOK_NAME = "book_name";
    public static final String COL_USER_ID = "user_id";

    // 建表SQL
    private static final String CREATE_TABLE_BOOK = "CREATE TABLE " + TABLE_BOOK + " ("
            + COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + COL_BOOK_NUMBER + " TEXT NOT NULL UNIQUE, "
            + COL_BOOK_NAME + " TEXT NOT NULL, "
            + COL_USER_ID + " TEXT NOT NULL)";

    public BookDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE_BOOK);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_BOOK);
        onCreate(db);
    }

    // 新增书籍
    public long addBook(Book book) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COL_BOOK_NUMBER, book.getBookNumber());
        values.put(COL_BOOK_NAME, book.getBookName());
        values.put(COL_USER_ID, book.getUserId());
        long result = db.insert(TABLE_BOOK, null, values);
        db.close();
        return result;
    }

    // 删除书籍
    public int deleteBook(String bookNumber, String userId) {
        SQLiteDatabase db = getWritableDatabase();
        String whereClause = COL_BOOK_NUMBER + "=? AND " + COL_USER_ID + "=?";
        String[] whereArgs = {bookNumber, userId};
        int rowsDeleted = db.delete(TABLE_BOOK, whereClause, whereArgs);
        db.close();
        return rowsDeleted;
    }

    // 更新书籍
    public int updateBook(String bookNumber, String newBookName, String userId) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COL_BOOK_NAME, newBookName);
        String whereClause = COL_BOOK_NUMBER + "=? AND " + COL_USER_ID + "=?";
        String[] whereArgs = {bookNumber, userId};
        int rowsUpdated = db.update(TABLE_BOOK, values, whereClause, whereArgs);
        db.close();
        return rowsUpdated;
    }

    // 查询书籍
    public List<Book> queryBooks(String bookNumber, String userId) {
        List<Book> bookList = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = null;

        try {
            String[] projection = {COL_BOOK_NUMBER, COL_BOOK_NAME, COL_USER_ID};
            String selection;
            String[] selectionArgs;

            if (bookNumber == null || bookNumber.isEmpty()) {
                selection = COL_USER_ID + "=?";
                selectionArgs = new String[]{userId};
            } else {
                selection = COL_BOOK_NUMBER + "=? AND " + COL_USER_ID + "=?";
                selectionArgs = new String[]{bookNumber, userId};
            }

            cursor = db.query(
                    TABLE_BOOK,
                    projection,
                    selection,
                    selectionArgs,
                    null,
                    null,
                    COL_BOOK_NUMBER + " ASC"
            );

            while (cursor.moveToNext()) {
                String bn = cursor.getString(cursor.getColumnIndexOrThrow(COL_BOOK_NUMBER));
                String bnName = cursor.getString(cursor.getColumnIndexOrThrow(COL_BOOK_NAME));
                String uid = cursor.getString(cursor.getColumnIndexOrThrow(COL_USER_ID));
                bookList.add(new Book(bn, bnName, uid));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) cursor.close();
            db.close();
        }
        return bookList;
    }
}
    